package com.dygg.bigfish.dao;

import com.dygg.bigfish.entity.Content;
import com.dygg.bigfish.entity.Invitation;
import com.dygg.bigfish.util.JDBCUtils;

import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

public class InvitationDao {

    //主界面查询：查询帖子表(bigFish_Invitation)里的所有数据，用list<Invitation>装
    public List<Invitation> queryAllInvitations (){

        String sql="SELECT " +
                "inv.*, " +
                "creator.user_name AS creatorName, " +
                "updater.user_name AS updaterName  " +
                "FROM " +
                "bigFish_Invitation inv " +
                "LEFT JOIN bigfish_user creator ON inv.Invitation_creatorID = creator.user_id " +
                "LEFT JOIN bigfish_user updater ON inv.Invitation_updaterId = updater.user_id ";
        ResultSet rs= JDBCUtils.doQuery(sql);
        List<Invitation> invitations = new ArrayList<>();

        try{
            while (rs.next()){
                Invitation invitation  = new Invitation();
                invitation.setId(rs.getString("Invitation_id"));
                invitation.setSubject(rs.getString("Invitation_subject"));
                invitation.setCreatorId(rs.getInt("Invitation_creatorID"));
                invitation.setCreatAt(rs.getString("Invitation_creatAt"));
                invitation.setCreatorName(rs.getString("creatorName"));
                invitation.setContentCount(rs.getInt("Invitation_contentCount"));
                invitation.setUpdateAt(rs.getString("Invitation_updateAt"));
                invitation.setUpdaterId(rs.getInt("Invitation_updaterId"));
                invitation.setUpdaterName(rs.getString("updaterName"));
                invitation.setTopMark(rs.getString("Invitation_topMark"));
                invitation.setPrimeMark(rs.getString("Invitation_primeMark"));
                invitation.setLockMark(rs.getString("Invitation_lockMark"));
                invitation.setReversalMark(rs.getString("Invitation_reversalMark"));
                invitations.add(invitation);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.doClose(rs);
        }
        return  invitations;
    }

    //查询帖子：根据帖子ID查询帖子表(bigFish_Invitation)里的数据
    public Invitation queryInvitationsById (String id){

        String sql="SELECT " +
                "inv.*, " +
                "creator.user_name AS creatorName, " +
                "updater.user_name AS updaterName  " +
                "FROM " +
                "bigFish_Invitation inv " +
                "LEFT JOIN bigfish_user creator ON inv.Invitation_creatorID = creator.user_id " +
                "LEFT JOIN bigfish_user updater ON inv.Invitation_updaterId = updater.user_id " +
                "WHERE " +
                "inv.Invitation_id = ? ";
        ResultSet rs= JDBCUtils.doQuery(sql,id);

        Invitation invitation  = new Invitation();
        try{
            if (rs.next()){
                invitation.setId(rs.getString("Invitation_id"));
                invitation.setSubject(rs.getString("Invitation_subject"));
                invitation.setCreatorId(rs.getInt("Invitation_creatorID"));
                invitation.setCreatAt(rs.getString("Invitation_creatAt"));
                invitation.setCreatorName(rs.getString("creatorName"));
                invitation.setContentCount(rs.getInt("Invitation_contentCount"));
                invitation.setUpdateAt(rs.getString("Invitation_updateAt"));
                invitation.setUpdaterId(rs.getInt("Invitation_updaterId"));
                invitation.setUpdaterName(rs.getString("updaterName"));
                invitation.setTopMark(rs.getString("Invitation_topMark"));
                invitation.setPrimeMark(rs.getString("Invitation_primeMark"));
                invitation.setLockMark(rs.getString("Invitation_lockMark"));
                invitation.setReversalMark(rs.getString("Invitation_reversalMark"));
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.doClose(rs);
        }
        return  invitation;
    }

    //查询帖子：根据帖子id查询帖子内容表(bigFish_content)里的所有数据，用list<Content>装
    public List<Content> queryContentByInvitationId (String id){

        String sql="SELECT " +
                "con.*, " +
                "updater.user_name AS updaterName " +
                "FROM " +
                "bigfish_content con " +
                "LEFT JOIN bigfish_user updater ON con.content_updaterId = updater.user_id " +
                "WHERE " +
                "con.content_Invid = ?";
        ResultSet rs= JDBCUtils.doQuery(sql,id);
        List<Content> contents = new ArrayList<>();

        try{
            while (rs.next()){
                Content content  = new Content();
                content.setId(rs.getInt("content_id"));
                content.setInvitationID(rs.getString("content_InvId"));
                content.setContentText(rs.getString("content_text"));
                content.setUpdaterID(rs.getInt("content_updaterId"));
                content.setUpdaterName(rs.getString("updaterName"));
                content.setUpdateAt(rs.getTimestamp("content_updateAt"));
                contents.add(content);
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.doClose(rs);
        }
        return  contents;
    }

    //根据时间自动生成帖子ID，
    public static String getSystemId(){
        //获取当前时间，转换成固定长度的数字
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String newDate = sdf.format(new Date());
        // 获取当前时间戳
        long timestamp = System.currentTimeMillis();
        // 使用当前时间戳作为种子来生成随机数
        Random random = new Random(timestamp);
        // 生成一个 0 到 9999 之间的随机整数
        int randomNumber = random.nextInt(10000);
        // 格式化成四位数的字符串（前面补零）
        String RandomNumber = String.format("%04d", randomNumber);

        return newDate+RandomNumber;
    }

    //获取当前时间，格式为yyyy-MM-dd HH:mm:ss
    public static String getNowTime(){
        SimpleDateFormat sdf = new SimpleDateFormat();// 格式化时间
        sdf.applyPattern("yyyy-MM-dd HH:mm:ss");// a为am/pm的标记
        Date date = new Date();// 获取当前时间
//        System.out.println("现在时间：" + sdf.format(date)); // 输出已经格式化的现在时间（24小时制）

        return sdf.format(date);
    }

    //发帖：增加一个数据到帖子表(bigFish_Invitation)
    public boolean InsertPost(String Invitationid,String subjectInput,int Userid) {
        String DateTime = getNowTime(); //当前时间
        String sql1 = "INSERT INTO bigfish_invitation (Invitation_id, Invitation_subject, Invitation_creatorID, Invitation_creatAt, Invitation_contentCount, Invitation_updateAt, Invitation_updaterId, Invitation_topMark, Invitation_primeMark, Invitation_lockMark, Invitation_reversalMark ) " +
                "VALUES " +
                "(?,?,?,?,1,?,?,0,0,0,0)";
        int count = JDBCUtils.doUpdate(sql1,Invitationid,subjectInput,Userid,DateTime,DateTime,Userid);
        return count>0 ;
    }

    //发帖/回帖：增加一个数据到帖子内容表(bigFish_Content)
    public boolean addPost(String Invitationid,String contentInput,int Userid) {
        String DateTime = getNowTime(); //当前时间
        String sql = "INSERT INTO bigfish_content ( content_Invid, content_text, content_updaterId, content_updateAt ) " +
                " VALUES " +
                " (?,?,?,?)";
        int count = JDBCUtils.doUpdate(sql,Invitationid,contentInput,Userid,DateTime);
        return count>0 ;
    }
    //回帖：修改帖子表(bigFish_Invitation),包括是否为置顶帖，后面为空则不变
    public boolean modifyPost(String Invitationid,int Userid) {
        String DateTime = getNowTime(); //当前时间
        String sql = "UPDATE bigfish_invitation " +
                "SET Invitation_contentCount = Invitation_contentCount + 1, " +
                "Invitation_updateAt = ?, " +
                "Invitation_updaterId = ? " +
                "WHERE " +
                "Invitation_id = ?";
        int count = JDBCUtils.doUpdate(sql, DateTime, Userid, Invitationid);
        return count>0 ;
    }

    //删除整个帖子
    public boolean deletePost(String Invitationid) {
        String sql = "delete from bigFish_Invitation where Invitation_id = ?";
        int count = JDBCUtils.doUpdate(sql,Invitationid);
        return count>0 ;
    }

    //删除贴子里的特定楼层（如果楼层ID为空则视为删除所有楼层）
    public boolean deletePostContent(String Invitationid,String contentid) {
        int count = 0;
        if(contentid ==null || contentid == "") {
            String sql = "delete from bigFish_content where content_InvId";
            count = JDBCUtils.doUpdate(sql, Invitationid);
        }
        else {
            String sql = "delete from bigFish_content where content_InvId = ? and content_id = ?";
            count = JDBCUtils.doUpdate(sql, Invitationid, contentid);
        }
        return count>0 ;
    }

    public boolean contentCountDown(String Invitationid) {
        String sql = "UPDATE bigfish_invitation " +
                "SET Invitation_contentCount = Invitation_contentCount - 1 " +
                "WHERE " +
                "Invitation_id = ?";
        int count = JDBCUtils.doUpdate(sql, Invitationid);
        return count>0 ;
    }

    //切换标记
    // 根据描述调用4种
    //topMark 置顶帖标志
    //primeMark 精华帖标志
    //lockMark 锁定标志
    //reversalMark 反向显示标志
    // (0=false，1=true)
    public boolean changeToMark(String Invitationid,String topMark,String primeMark,String lockMark,String reversalMark) {
        String sql = "UPDATE bigfish_invitation  " +
                "SET Invitation_topMark = ? , " +
                "Invitation_primeMark = ? , " +
                "Invitation_lockMark = ? , " +
                "Invitation_reversalMark = ? " +
                "WHERE " +
                "Invitation_id = ?";
        int count = JDBCUtils.doUpdate(sql,topMark,primeMark,lockMark,reversalMark, Invitationid);
        return count>0 ;
    }
}
